﻿using Corina.Datamodel.Base;
using Corina.Datamodel.Entities;
using Corina.Enumerations;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Corina.DataContext.Service
{
    public class ThirdPartyMeasureService : ServiceBase
    {
        public DataTable GetAllItems(string connectionString, int userId, string specimen, int specimenKind, string measureObject, string applyUnit, List<QueryCondition> conditions, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";
            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY SubmitTime DESC) AS ROW_NUMBER,measure.*,tu.Name as UserName,tsk.Title SpecimenKind,tns.Code AS Guobiao, tns.DocumentName, tnsc.PreviewImage ");
            builder.Append("  FROM t_third_party_measure AS measure ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_specimen_kind AS tsk ON tsk.Id = measure.SpecimenKindId ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_national_standard_config AS tnsc ON measure.MeasureObject = tnsc.Name  AND measure.SpecimenKindId = tnsc.SpecimenKindId  ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_national_standard AS tns ON tns.Id = tnsc.DocumentId  ");
            builder.Append("LEFT JOIN CORINA_ACCO.dbo.t_user AS tu ON tu.Id = measure.UserId ");
            builder.Append("WHERE 1=1 ");

            #region Conditions
            string _subidString = string.Empty;

            if (userId > 0)
            {
                _subidString = " WHERE measure.UserId = @UserId";
                paras.Add(new SqlParameter("@UserId", userId));
            }

            builder.Append("AND measure.ApplyUnit LIKE @ApplyUnit ");
            paras.Add(new SqlParameter("@ApplyUnit", string.Format("%{0}%", applyUnit)));

            builder.Append("AND measure.MeasureObject LIKE @MeasureObject ");
            paras.Add(new SqlParameter("@MeasureObject", string.Format("%{0}%", measureObject)));

            builder.Append("AND measure.Specimen LIKE @Specimen ");
            paras.Add(new SqlParameter("@Specimen", string.Format("%{0}%", specimen)));

            if (specimenKind > 0)
            {
                builder.Append("AND measure.SpecimenKindId = @SpecimenKind ");
                paras.Add(new SqlParameter("@SpecimenKind", specimenKind));
            }


            if (conditions.Count > 0)
            {
                conditions.ForEach(t =>
                {
                    if (t.IsValid)
                    {
                        switch (t.Symbol)
                        {
                            case ConditionValueSymbol.Between:
                                builder.AppendFormat(" AND ( measure.{0} BETWEEN @{0}Start AND @{0}End ) ", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter + "Start", t.ValueStart));
                                paras.Add(new SqlParameter("@" + t.Parameter + "End", t.ValueEnd));
                                break;
                            case ConditionValueSymbol.Equals:
                                builder.AppendFormat(" AND measure.{0} = @{0}", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter, t.ValueStart));
                                break;
                            case ConditionValueSymbol.Greater:
                                builder.AppendFormat(" AND measure.{0} > @{0}", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter, t.ValueStart));
                                break;
                            case ConditionValueSymbol.Smaller:
                                builder.AppendFormat(" AND measure.{0} < @{0}", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter, t.ValueEnd));
                                break;
                        }
                    }
                });
            }
            #endregion

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}", cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}", qstring, builder), paras.ToArray());
            return table;
        }

        public DataTable GetAllItems(string connectionString, int userId, int subid, string specimen,int specimenKind, string measureObject, string tester, List<QueryCondition> conditions, int index, int size, ref int count)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            string qstring = "SELECT * FROM ( ";
            string cstring = "SELECT COUNT(*) FROM ( ";

            paras.Add(new SqlParameter("@Supid", userId));
            StringBuilder ctestring = new StringBuilder();
            ctestring.Append(";WITH cte AS ( ");
            ctestring.Append("	SELECT tuh.Id, tuh.Subid, tuh.SuperiorId FROM CORINA_ACCO.dbo.t_user_hypotaxis AS tuh ");
            ctestring.Append("	WHERE 1=1 ");
            if (userId > 0)
            {
                ctestring.Append(" AND tuh.SuperiorId = @Supid  OR tuh.Subid = @Supid");
            }
            ctestring.Append("	UNION ALL ");
            ctestring.Append("	SELECT tuh_u.Id, tuh_u.Subid, tuh_u.SuperiorId ");
            ctestring.Append("	FROM CORINA_ACCO.dbo.t_user_hypotaxis tuh_u  ");
            ctestring.Append("	JOIN cte AS tuh_2 ON tuh_u.SuperiorId = tuh_2.Subid ) ");

            StringBuilder builder = new StringBuilder();

            builder.Append("SELECT ROW_NUMBER() OVER (ORDER BY SubmitTime DESC) AS ROW_NUMBER,measure.*,tu.Name as UserName,tsk.Title SpecimenKind,tns.Code AS Guobiao, tns.DocumentName, tnsc.PreviewImage ");
            builder.Append("  FROM t_third_party_measure AS measure ");
            builder.Append("LEFT JOIN CORINA_PROD.dbo.t_specimen_kind AS tsk ON tsk.Id = measure.SpecimenKindId ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_national_standard_config AS tnsc ON measure.MeasureObject = tnsc.Name  AND measure.SpecimenKindId = tnsc.SpecimenKindId  ");
            builder.Append("LEFT JOIN CORINA_BASE.dbo.t_national_standard AS tns ON tns.Id = tnsc.DocumentId  ");
            builder.Append("LEFT JOIN CORINA_ACCO.dbo.t_user AS tu ON tu.Id = measure.UserId ");
            builder.Append("WHERE 1=1 ");

            #region Conditions
            string _subidString = string.Empty;

            if (subid > 0)
            {
                _subidString = " WHERE cte.Subid = @Subid";
                paras.Add(new SqlParameter("@Subid", subid));
            }

            builder.AppendFormat(" AND (tu.Id IN (SELECT cte.Subid FROM cte {0} ) {1})", _subidString, userId > 0 ? "OR tu.Id = @Supid" : "");

            //builder.Append("AND td.DeviceSn LIKE @DeviceSn ");
            //paras.Add(new SqlParameter("@DeviceSn", string.Format("%{0}%", deviceSn)));

            builder.Append("AND measure.MeasureObject LIKE @MeasureObject ");
            paras.Add(new SqlParameter("@MeasureObject", string.Format("%{0}%", measureObject)));

            builder.Append("AND measure.Specimen LIKE @Specimen ");
            paras.Add(new SqlParameter("@Specimen", string.Format("%{0}%", specimen)));

            if (specimenKind > 0)
            {
                builder.Append("AND measure.SpecimenKindId = @SpecimenKind ");
                paras.Add(new SqlParameter("@SpecimenKind", specimenKind));
            }

            if (!string.IsNullOrEmpty(tester))
            {
                builder.Append("AND measure.Tester = @Tester ");
                paras.Add(new SqlParameter("@Tester", tester));
            }

            

            //if (userId > 0)
            //{
            //    builder.Append("AND tu.Id = @UserId ");
            //    paras.Add(new SqlParameter("@UserId", userId));
            //}

            if (conditions.Count > 0)
            {
                conditions.ForEach(t =>
                {
                    if (t.IsValid)
                    {
                        switch (t.Symbol)
                        {
                            case ConditionValueSymbol.Between:
                                builder.AppendFormat(" AND ( measure.{0} BETWEEN @{0}Start AND @{0}End ) ", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter + "Start", t.ValueStart));
                                paras.Add(new SqlParameter("@" + t.Parameter + "End", t.ValueEnd));
                                break;
                            case ConditionValueSymbol.Equals:
                                builder.AppendFormat(" AND measure.{0} = @{0}", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter, t.ValueStart));
                                break;
                            case ConditionValueSymbol.Greater:
                                builder.AppendFormat(" AND measure.{0} > @{0}", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter, t.ValueStart));
                                break;
                            case ConditionValueSymbol.Smaller:
                                builder.AppendFormat(" AND measure.{0} < @{0}", t.Parameter);
                                paras.Add(new SqlParameter("@" + t.Parameter, t.ValueEnd));
                                break;
                        }
                    }
                });
            }
            #endregion

            builder.Append(") AS query ");

            count = DbOperator.GetCount(connectionString, string.Format("{0}{1}{2}", ctestring, cstring, builder), paras.ToArray());

            builder.Append("WHERE query.[ROW_NUMBER] BETWEEN @Index + 1 AND @Index + @Size ");
            paras.Add(new SqlParameter("@Index", (index - 1) * size));
            paras.Add(new SqlParameter("@Size", size));
            builder.Append("ORDER BY query.[ROW_NUMBER]");
            Logis.LogHelper.Instance.Write(string.Format("{0}{1}", qstring, builder), paras.ToArray());

            DataTable table = DbOperator.GetDataTable(connectionString, string.Format("{0}{1}{2}", ctestring, qstring, builder), paras.ToArray());
            return table;
        }

        public bool Insert(string connectionString, ThirdPartyMeasureInfo entity)
        {
            return DbOperator.UpdateEntity(connectionString, entity, EntityOperation.Insert);
        }
    }
}
